USE SAS
GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'GetOpportunitiesInProgress')
	BEGIN
		DROP  Procedure  dbo.GetOpportunitiesInProgress
	END

GO

CREATE Procedure dbo.GetOpportunitiesInProgress
	@userId INT
AS
BEGIN
	;WITH A AS (
		-- OPPORTUNITY HEADER
		SELECT
			op.[Description]				AS OpportunityName
		,	op.OppRevenue					AS Revenue
		,	op.OppGuid						AS PKID
		FROM TR_OPPORTUNITYH as op WITH (NOLOCK)
		INNER JOIN TR_Relationships as r
			ON r.SourceGUID = op.OppGuid
		WHERE
			r.Partner2 = @userID
		AND r.RelationshipType = 3		-- OWNER
		AND op.Status = 3 -- IN PROGRESS
	), B AS (
		-- ACCOUNT
		SELECT
			acc.PartnerName					AS AccountName
		,	A.PKID
		FROM TR_PARTNER AS acc WITH (NOLOCK)
		INNER JOIN TR_RELATIONSHIPS AS r WITH (NOLOCK)
			ON r.PARTNER2 = acc.PartnerNo
		INNER JOIN A
			ON A.PKID = R.SourceGUID
		WHERE
			r.RelationshipType = 11 -- OPPORTUNITY PARTNER
	), FINAL AS (
		SELECT
			B.AccountName				AS [Account Name]
		,	A.OpportunityName			AS [Description]
		,	A.Revenue
		,	A.PKID
		FROM A
		LEFT OUTER JOIN B
			ON B.PKID = A.PKID
	)

	SELECT * FROM FINAL

END
GO